-- Start of DDL Script for Package PEMEX.PKG_REPORTES
-- Generated 09/04/2012 23:08:45 from PEMEX@ORCL

CREATE OR REPLACE 
PACKAGE pkg_reportes
IS
   PROCEDURE rptportadaexpediente (
      pcve_solicitud_afectado   IN       VARCHAR2,
      pcve_clave_trabajo_realizar   IN       VARCHAR2,
      resultado                 OUT      sys_refcursor
   );

   PROCEDURE permiso_paso (
      pcve_solicitud_afectado       IN       VARCHAR2,
      pcve_clave_trabajo_realizar   IN       VARCHAR2,
      resultado                     OUT      sys_refcursor
   );
END;
/


CREATE OR REPLACE 
PACKAGE BODY pkg_reportes
IS
   PROCEDURE rptportadaexpediente (
      pcve_solicitud_afectado   IN       VARCHAR2,
      pcve_clave_trabajo_realizar   IN       VARCHAR2,
      resultado                 OUT      sys_refcursor
   )
   IS
      vsql   VARCHAR2 (4000);
   BEGIN
      vsql :=
            'SELECT 
                    cpre.denominado,
                    cpre.norte,
                    cpre.sur,
                    cpre.este,
                    cpre.oeste,
                    cpre.superficie,
                    cpre.acreditado,
                    cpre.acreditado_no,
                    to_char(cpre.acreditado_fecha,''dd/mm/yyyy'')fecha,
                    sa.no_expediente,
                    cp.nombre ||'' ''||cp.apellido_p||'' ''||cp.apellido_m as nombre_completo,
                    cp.rfc,
                    cp.clave_elector,
                    cm.descripcion as  municipio,
                    clo.descripcion as localidad,
                    ''Tabasco'' estado,
                    cts.descripcion tipo_reclamo,
                    ''$ ''||trim(to_char(sa.monto_por_afectacion_aprobado,''999,999,999.99'')) monto,
                    obra.desc_obra, obra.origen inicio, obra.destino, obra.longitud,obra.ANCHO_DERECHO_VIA,
                    sa.area_afectada superficie_afectada,
                    sol.no_oficio, to_char(sol.fecha,''dd/mm/yyyy'') fecha_oficio,
                    soli.descripcion area_solicitante,
                    (
                        SELECT  A.NOMBRE||'' ''||A.APELLIDO_P||'' ''||a.APELLIDO_M
                        FROM    CAT_USUARIO A,SOLICITUD_TURNAR B
                        WHERE   A.CVE_USUARIO           = B.cve_usuario
                        AND     B.cve_trabajo_realizar  = obra.cve_trabajo_Realizar
                        AND     a.cve_rol               = 2 
                    ) SUPERVISOR_AUTORIZADO,
                    (
                        SELECT  A.ficha
                        FROM    CAT_USUARIO A,SOLICITUD_TURNAR B
                        WHERE   A.CVE_USUARIO           = B.cve_usuario
                        AND     B.cve_trabajo_realizar  = obra.cve_trabajo_Realizar
                        AND     a.cve_rol               = 2 
                    ) FICHA_SUPERVISOR,
                    (
                        SELECT  A.no_cedula
                        FROM    CAT_USUARIO A,SOLICITUD_TURNAR B
                        WHERE   A.CVE_USUARIO           = B.cve_usuario
                        AND     B.cve_trabajo_realizar  = obra.cve_trabajo_Realizar
                        AND     a.cve_rol               = 2 -- SUPERVISOR
                    ) CEDULA_SUPERVISOR,
                    usu.NOMBRE||'' ''||usu.APELLIDO_P||'' ''||usu.APELLIDO_M GESTOR,
                    usu.ficha FICHA_GESTOR,
                    usu.NO_cedula CEDULA_GESTOR,
                    sa.observaciones,
                   TO_CHAR (SYSDATE, ''fmDD "de" Month "de" YYYY'') FECHA_OBTENCION_PER_PASO
  FROM cat_personas cp,
       cat_predio cpre,
       solicitud_afectado sa,
       cat_municipio cm,
       cat_localidades clo,
       solicitud_trabajos_realizar obra,
       solicitud sol,
       cat_solicitante soli,
       cat_usuario usu,
       cat_tipo_servicio cts
 WHERE cp.cve_afectado = cpre.cve_afectado
   AND sa.cve_predio = cpre.cve_predio
   AND clo.cve_localidad = cpre.cve_localidad

   AND clo.cve_municipio = CM.cve_municipio
   
   AND obra.cve_solicitud = sol.cve_solicitud
   AND sol.cve_solicitante = soli.cve_solicitante
   AND sa.cve_usuario = usu.cve_usuario
   AND sa.cve_trabajo_realizar = obra.cve_trabajo_realizar
   and obra.cve_tipo_servicio = cts.cve_tipo_servicio(+) ';
   
        
      IF (pcve_clave_trabajo_realizar IS NOT NULL and length(pcve_clave_trabajo_realizar) > 0)
      THEN
         vsql :=
               vsql
            || ' AND sa.cve_trabajo_realizar='
            || pcve_clave_trabajo_realizar;
      END IF;

      IF (pcve_solicitud_afectado IS NOT NULL and length(pcve_solicitud_afectado) > 0)
      THEN
         vsql :=
               vsql
            || ' AND sa.cve_solicitud_afectado IN (' || pcve_solicitud_afectado || ')';
      END IF;

      
      OPEN resultado FOR vsql;
   END;

   PROCEDURE permiso_paso (
      pcve_solicitud_afectado       IN       VARCHAR2,
      pcve_clave_trabajo_realizar   IN       VARCHAR2,
      resultado                     OUT      sys_refcursor
   )
   IS
      vsql   VARCHAR2 (4000);
   BEGIN
        
       IF (pcve_solicitud_afectado IS NOT NULL and length(pcve_solicitud_afectado) > 0)
      THEN
         pkg_envio_pp.generarfolio (pcve_solicitud_afectado,
                                    'pcve_usuario_mtto'
                                   );
         COMMIT;
      END IF;

      vsql :=
         'select 
            cp.cve_afectado,
            str.cve_solicitud
            ,str.cve_trabajo_realizar
            , str.desc_obra
            , str.diametro 
            , sa.area_afectada as superficie
            ,cp.denominado
            ,catp.nombre ||'' '' ||catp.apellido_p ||'' ''|| catp.apellido_m as nombre_completo
            ,cp.cve_localidad
            ,localidad.descripcion as localidad
            ,municipio.descripcion as minicpio
            ,sol.numero_nota_turno
            ,cs.destinatario_con_copia
            ,upper(sol.no_oficio) as no_oficio
            ,to_char(sol.fecha_oficio,''dd/mm/yyyy'') as fecha_oficio
            , sol.nombre_solicitante
            , cs.descripcion as area_solicitante
            
            , (SELECT NOMBRE||'' ''||APELLIDO_P||'' ''||APELLIDO_M FROM CAT_USUARIO A, SOLICITUD_TURNAR B WHERE A.CVE_ROL=1
                AND A.cve_usuario = B.cve_usuario AND B.cve_trabajo_realizar = str.cve_trabajo_realizar) NOMBRE_JEFE_SECTOR
            , (SELECT initcap(A.desc_sector) FROM CAT_USUARIO A, SOLICITUD_TURNAR B WHERE A.CVE_ROL=1
                AND A.cve_usuario = B.cve_usuario AND B.cve_trabajo_realizar = str.cve_trabajo_realizar) SECCION_JEFE_SECTOR
            , (SELECT a.extension FROM CAT_USUARIO A, SOLICITUD_TURNAR B WHERE A.CVE_ROL=1
                AND A.cve_usuario = B.cve_usuario AND B.cve_trabajo_realizar = str.cve_trabajo_realizar) EXTENSION_JEFE_SECTOR
            , ENVIO_PERMISO_PASO.no_oficio Folio
    from solicitud_trabajos_realizar str
        , solicitud_afectado sa
        , cat_predio cp, cat_personas catp
        , cat_localidades localidad
        , cat_municipio municipio 
        , solicitud sol
        , cat_solicitante cs
        , AFECTADO_ENVIO_PP
        , ENVIO_PERMISO_PASO
    where 
    str.cve_trabajo_realizar=sa.cve_trabajo_realizar
    and sa.cve_predio=cp.cve_predio
    and catp.cve_afectado=cp.cve_afectado
    and localidad.cve_municipio=municipio.cve_municipio
    and cp.cve_localidad=localidad.cve_localidad
    and str.cve_solicitud=sol.cve_solicitud
    and sol.cve_solicitante= cs.cve_solicitante
    AND SA.cve_solicitud_afectado =AFECTADO_ENVIO_PP.cve_solicitud_afectado
    AND AFECTADO_ENVIO_PP.fecha_registro = (SELECT MAX(fecha_registro) FROM AFECTADO_ENVIO_PP WHERE cve_solicitud_afectado = SA.cve_solicitud_afectado)
    AND AFECTADO_ENVIO_PP.cve_envio_pp = ENVIO_PERMISO_PASO.cve_envio_pp    ';

       IF (pcve_solicitud_afectado IS NOT NULL and length(pcve_solicitud_afectado) > 0)
      THEN
      insert into paso(valor) values('Jonas Entro1' + vsql);
         vsql :=
               vsql
            || ' AND sa.cve_solicitud_afectado in ('
            || pcve_solicitud_afectado
            || ') ';
      END IF;

       IF (pcve_clave_trabajo_realizar IS NOT NULL and length(pcve_clave_trabajo_realizar) > 0)
      THEN
            insert into paso(valor) values('Jonas Entro2' + vsql);
         vsql :=
               vsql
            || ' AND str.cve_trabajo_realizar='
            || pcve_clave_trabajo_realizar;
      END IF;
      
      insert into paso(valor) values('Jonas' + vsql);

      OPEN resultado FOR vsql;
   END;
END;
/


-- End of DDL Script for Package PEMEX.PKG_REPORTES

